package com.admin.service.bookservice;

import com.admin.dao.bookdao.BookDaoImpl;
import com.admin.dao.bookdao.BookDaoInF;
import com.admin.dao.booktypedao.BookTypeDaoImpl;
import com.admin.dto.BookListBean;
import com.admin.dto.BookLossBean;
import com.admin.dto.QueryReturnListBean;
import com.pojo.BooksBean;
import com.utils.DBUtil;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookServiceImpl implements BookServiceInF{
    BookDaoInF bookDaoInF=null;
    public BookServiceImpl(){
        bookDaoInF =new BookDaoImpl();

    }

    @Override
    public boolean insertBook(BooksBean booksBean) {
        boolean bool = false;
        Connection conn = null;

        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);////开启了 事务
            //INSERT INTO `books` VALUES (2, '红楼梦', 25.00, 2.00, '曹雪芹', '人民出版社', NULL, 10, 1, '0', NULL, NULL, NULL, NULL);
            String sql="INSERT INTO `books` VALUES (null, ?, ?, ?,?," +
                    " ?,?, ?, ?, ?, ?, ?,?, ?)";

            bool = bookDaoInF.InsertInfo(sql, conn, booksBean);

            if (bool) {
                conn.commit();
            } else {
                conn.rollback();
            }

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return bool;

    }

    @Override
    public List<BookListBean> bookList(int pageIndex, int pageSize) {
        Connection conn=null;
        List<BookListBean> list=null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "SELECT b.bookid,b.bookname,b.price,b.borrowprice,b.author,b.press,b.publicdate,b.stock,b.Originalstock,t.typename,b.booknumber,b.address\n" +
                    "from books b,booktype t where b.type=t.typeid  and b.islower='0' limit ?,?";
            int offset = (pageIndex-1)*pageSize;//查询id开始
            list = bookDaoInF.getbookList(sql, conn,offset,pageSize);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return list;


    }

    @Override
    public int getCount() {

        Connection conn=null;
       int count=0;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select count(bookid) count from books";
            count = bookDaoInF.getBookSum(sql, conn );
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return count;

    }

    public  boolean delBooks(int bookid){
        boolean bool = false;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "UPDATE  books set islower='1' where bookid=? ";
            //设置登录状态，先查表是否登录，若未登录更改表为登录状态
            bool = bookDaoInF.delBookInfoById(sql, conn, bookid);
            if (bool) {
                conn.commit();
            } else {
                conn.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return bool;
    }
    public  boolean alterBooks(BooksBean booksBean){
        boolean bool = false;
        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "UPDATE  books set booknumber=?,bookname=?,press=?,price=?,borrowprice=?,author=?\n" +
                    ",type=?,stock=?\n" +
                    "where bookid=? ";
            //设置登录状态，先查表是否登录，若未登录更改表为登录状态
            bool = bookDaoInF.alterBookInfoById(sql, conn, booksBean);
            if (bool) {
                conn.commit();
            } else {
                conn.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }

        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return bool;
    }


    @Override
    public List<QueryReturnListBean> QueryAllBookInfoByName(String idCard) {
        List<QueryReturnListBean> list = new ArrayList<>();

        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select bookname,press,address,bordate,repaydate,borinfo.bookid bid from borrower,borinfo,books WHERE idcard=? and userid=borid and borinfo.bookid=books.bookid";
            list = bookDaoInF.getAllBookInfo(sql, conn,idCard);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    /**
     * 直接查询所有书籍信息
     * private String bookId;
     *     private String bookName;
     *     private String author;
     *     private String press;
     *     private String bookType;
     *     private String stock;//库存
     *     private String price;//价格
     * @return
     */
    @Override
    public List<BookListBean> queryAllBookInfo() {
        List<BookListBean> list = new ArrayList<>();

        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select bookid,bookname,author,press,typename,stock,borrowprice from books,booktype where type=typeid";
            list = bookDaoInF.queryAllBookInfo(sql, conn);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    /**
     * 根据书id查寻书籍信息
     * @param bookId
     * @return
     */
    @Override
    public BooksBean queryBookInfoByBookId(String bookId) {
        Connection conn = null;
        BooksBean booksBean = new BooksBean();
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "select * from books where bookid=?";
            booksBean = bookDaoInF.queryBookById(sql, conn,bookId);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return booksBean;
    }

    @Override
    public Boolean queryBorrowerByBookId(String idCard) {
        return null;
    }

    @Override
   public List<BookListBean> queryBookInfoByBookName(String bookName) {
        List<BookListBean> list = new ArrayList<>();

        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = " \n" +
                    " select bookid,bookname,author,press,typename,stock," +
                    "borrowprice from books,booktype where type=typeid and  books.islower='0'and bookname    " +
                    " like  concat('%',\'"+bookName+"\', '%');";
            list = bookDaoInF.queryAllBookInfo(sql, conn);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public List<BookLossBean> queryBookDemage() {
        List<BookLossBean> list = new ArrayList<>();

        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "SELECT   bor.idcard idcard, bd.bookdamagemsgid id,  b.bookname bname,b.price pr,b.booknumber bnum ,bd.damagedegree amge,bd.bookdamagemsg bmsg,bd.sendtime  st ,bor.username borname  from books b,bookdamage bd ,borrower bor where b.bookid=bd.bookid  and bd.checkstatus='1'\n" +
                    "and bor.userid=bd.userid";
            list = bookDaoInF.queryBookDemageInfo(sql, conn);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public String demageDegree(Integer id) {
        String zhuangtai=null;

        Connection conn = null;
        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "SELECT damagedegree from bookdamage where bookdamagemsgid=?";
            zhuangtai = bookDaoInF.demageDegreeDao(sql, conn,id);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return zhuangtai;
    }

    @Override
    public boolean updateStock(String bookId) {
        boolean bool=false;
        Connection conn = null;

        try {
            conn = DBUtil.getConn();
            conn.setAutoCommit(false);
            String sql = "update books set stock=stock-1 where bookid=? and stock!=0";
            bool = bookDaoInF.updateStock(sql, conn,bookId);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException throwable) {
                throwable.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return bool;
    }




}
